package sql;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
/**
 * this class Handles all customer sql procedurs
 * @author Ron Shenkerman
 * @author Ivan Onyshchenko
 */
public class CustomerDetails {
	/**
	 * get all customers details from db
	 * @return all customers details
	 */
	public ArrayList<String> getdetails() {
		ArrayList<String> arr=new ArrayList<String>();
		try 
		{
			Statement stmt;
			SQLConnection conn = new  SQLConnection();
			stmt = conn.getConnection().createStatement();
			
			ResultSet rs= stmt.executeQuery("SELECT * FROM mtl.customer ;");
				while (rs.next())
				{
					
					arr.add(rs.getString(1));
					arr.add(rs.getString(2));
					arr.add(rs.getString(3));
					arr.add(rs.getString(4));
					arr.add(rs.getString(5));
					arr.add(rs.getString(6));
					arr.add(rs.getString(7));
					arr.add(rs.getString(8));
					arr.add(rs.getString(9));
					
				}
			return arr;
		
		}
  
		catch (Exception ex )
		{
				ex.printStackTrace();
		}
		return null;
		
	}
/**
 * get specific customer details from db
 * @param cusid - customer id
 * @return all customer details
 */
	public ArrayList<String> getCustomerDetails(String cusid) {
		ArrayList<String> arr=new ArrayList<String>();
		 Statement stmt;
			SQLConnection conn = new  SQLConnection();
			
				try {
					stmt = conn.getConnection().createStatement();
				
				ResultSet rs= stmt.executeQuery("Select * from mtl.customer where cust_id = '"+cusid+"';");
				while (rs.next())
				{
				arr.add(rs.getString(1));
				arr.add(rs.getString(2));
				arr.add(rs.getString(3));
				arr.add(rs.getString(4));
				arr.add(rs.getString(5));
				arr.add(rs.getString(6));
				arr.add(rs.getString(7));
				arr.add(rs.getString(8));
				arr.add(rs.getString(9));
				}
			return arr;
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
	}
	/**
	 * insert customer to the db
	 * @param cusid - customer id
	 * @return all customer details
	 */
	public int adduser(ArrayList<String> cusarr) {
		try 
		{
			Statement stmt;
			SQLConnection conn = new  SQLConnection();
			stmt = conn.getConnection().createStatement();
			stmt.executeUpdate("insert into mtl.customer(name,country,city,address,tel,fax,email,account) values('"+cusarr.get(0)+"','"+cusarr.get(1)+"','"+cusarr.get(2)+"','"+cusarr.get(3)+"','"+cusarr.get(4)+"','"+cusarr.get(5)+"','"+cusarr.get(6)+"','"+cusarr.get(7)+"');");
				
			return 1;
		
		}
  
		catch (Exception ex )
		{
				ex.printStackTrace();
		}
		return 0;
	}
	/**
	 * update specific customer details
	 * @param strspd - array that stores detail for update
	 * @return 1- succseful ,2 - error
	 */
	public int customersDetailsUpdate(ArrayList<String> strspd) {
		Statement stmt1;
		SQLConnection conn = new  SQLConnection();
		try {
			stmt1 = conn.getConnection().createStatement();
			stmt1.executeUpdate("update  mtl.customer set name = '"+strspd.get(0)+"',country = '"+strspd.get(1)+"',city = '"+strspd.get(2)+"', address = '"+strspd.get(3)+"', tel = '"+strspd.get(4)+"', fax = '"+strspd.get(5)+"',email = '"+strspd.get(6)+"',account = '"+strspd.get(7)+"' where cust_id = '"+strspd.get(8)+"';");
			return 1;
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
/**
 * 
 * @param id - to delete
 * @return 1- succseful ,2 - error
 */
	public int deleteCustomerDB(String id) {
		try 
		{
			Statement stmt;
			SQLConnection conn = new  SQLConnection();
			stmt = conn.getConnection().createStatement();
			System.out.println("deleting");
			stmt.executeUpdate("delete from mtl.customer where cust_id = '"+id+"';");
			return 1;
		
		}
  
		catch (Exception ex )
		{
				ex.printStackTrace();
		}
		return 0;
	}
/**
 * 
 * @return all customer name from db
 */
	public ArrayList<String> getCusNamesList() {
		ArrayList<String> arr=new ArrayList<String>();
		try 
		{
			Statement stmt;
			SQLConnection conn = new  SQLConnection();
			stmt = conn.getConnection().createStatement();
			
			ResultSet rs= stmt.executeQuery("SELECT mtl.customer.name FROM mtl.customer ;");
				while (rs.next())
				{
					arr.add(rs.getString(1));
				}
			return arr;
		
		}
  
		catch (Exception ex )
		{
				ex.printStackTrace();
		}
		return null;
		
	}

}
